package com.dao;

import com.bean.User;

import java.sql.*;

public class JDBC {
    public static final String URL="jdbc:mysql://127.0.0.1:3306/hcl?CharacterEncoding=utf-8";
    public static final String USER="root";
    public static final String PASSWORD="root";
    public JDBC(){

    }
    public static void JDBC() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        }catch (ClassNotFoundException e){
            e.printStackTrace();
        }
    }
    public static Connection DatabaseConnect(){
        JDBC();
        Connection connection= null;
        try {
            return connection = DriverManager.getConnection(URL,USER,PASSWORD);
        }catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }
    public static boolean SelectTable(User user){
        JDBC();
        Connection connection = JDBC.DatabaseConnect();
        PreparedStatement preparedStatement = null;
        String sql = "select * from userlist where username = ? and password = ?";
        try {
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,user.getUsername());
            preparedStatement.setString(2,user.getPassword());
            ResultSet rs = preparedStatement.executeQuery();
            connection.commit();
            if (rs.next())
                return true;
            else
                return false;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if (preparedStatement !=null)
                    preparedStatement.close();
                if (connection != null)
                    connection.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        return false;
    }
    public static boolean CheckTable(User user){
        JDBC();
        Connection connection = JDBC.DatabaseConnect();
        PreparedStatement preparedStatement = null;
        String sql = "select * from userlist where username = ?";
        try {
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,user.getUsername());
            ResultSet rs = preparedStatement.executeQuery();
            connection.commit();
            if (rs.next())
                return true;
            else
                return false;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if (preparedStatement !=null)
                    preparedStatement.close();
                if (connection != null)
                    connection.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        return false;
    }
    public static void DeleteTable(User user){
        JDBC();
        Connection connection = JDBC.DatabaseConnect();
        PreparedStatement preparedStatement = null;
        String sql = "delete from userlist where username = ? and password = ?";
        try {
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,user.getUsername());
            preparedStatement.setString(2,user.getPassword());
            preparedStatement.execute();
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if(preparedStatement != null)
                    preparedStatement.close();
                if (connection != null)
                    connection.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
    }
    public static void InsertTable(User user){
        JDBC();
        Connection connection = JDBC.DatabaseConnect();
        PreparedStatement preparedStatement = null;
        String sql = "insert into userlist(username,password) value(?,?)";
        try {
            connection.setAutoCommit(false);
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,user.getUsername());
            preparedStatement.setString(2,user.getPassword());
            preparedStatement.execute();
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if(preparedStatement != null)
                    preparedStatement.close();
                if (connection != null)
                    connection.close();
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
    }
}
